package com.hua.Dao;

import com.hua.Model.UserInfo;
import com.mysql.cj.util.EscapeTokenizer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import com.hua.Model.*;

public class SqlService {
	// 核心SQL语句执行方法，增删改查
	// 增加
	public int register_user(UserInfo userinfo) throws SQLException {
		Connection conn = Mysql.GetConn();
		String sql = "insert into Users values(NULL,?,?,?,?,?,?,?)";
		PreparedStatement preparedStatement = conn.prepareStatement(sql);
		preparedStatement.setString(2, userinfo.getAccount());
		preparedStatement.setString(1, userinfo.getName());
		preparedStatement.setString(3, userinfo.getPassword());
		preparedStatement.setString(4, "落霞与孤鹜齐飞，秋水共长天一色");
		preparedStatement.setString(5, "https://pic.imgdb.cn/item/654dbe5ac458853aef3328a9.png");
		preparedStatement.setString(6, "");
		preparedStatement.setInt(7, 1024);
		//
		int count = preparedStatement.executeUpdate();
		Mysql.CloseConn(preparedStatement, null, conn);
		return count;
	}

	// 获取用户
	public List<UserInfo> log_in(String account, String pwd) throws SQLException {
		Connection conn = Mysql.GetConn();
		// 构造SQL语句
		String sql = ("SELECT * FROM Users where account=? and password=?");
		PreparedStatement query = conn.prepareStatement(sql);
		query.setString(1, account);
		query.setString(2, pwd);
		ResultSet rs = query.executeQuery();
		List<UserInfo> userinfo = new ArrayList<>();
		// 展开结果集数据库
		while (rs.next()) {
			UserInfo user = new UserInfo();
			user.setId(rs.getInt("id"));
			user.setAccount(rs.getString("account"));
			user.setName(rs.getString("name"));
			user.setPassword(rs.getString("password"));
			user.setKey(rs.getString("key"));
			user.setSign(rs.getString("sign"));
			user.setImg(rs.getString("img"));
			user.setStorage(rs.getInt("storage"));
			userinfo.add(user); //
		}
		Mysql.CloseConn(query, rs, conn);
		return userinfo;
	}

	// 账户获取是否存在用户
	public Boolean is_account(String phone) throws SQLException {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = Mysql.GetConn();
			String sql = "select id from Users where account=?";
			preparedStatement = conn.prepareStatement(sql);
			preparedStatement.setString(1, phone);
			ResultSet resultSet = preparedStatement.executeQuery();

			int count = 0;
			while (resultSet.next()) {
				count++;
			}

			return count > 0;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	// 查询文件
	public List<FileInfo> getFiles(String name, Integer id, String type) throws SQLException {
		Connection conn = Mysql.GetConn();
		PreparedStatement query = null;
		// 构造SQL语句，判别是查询，还是分类文件显示
		// id为1为共享账户
		if (id == 1) {
			if (type.equals("查询")) {
				// 模糊查询，同时查询自己的文件与共享文件
				String sql = ("SELECT * from Files where name like ? and public=1 ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setString(1, "%" + name + "%");
			} else if (type.equals("默认")) {
				// 默认展示，显示自己的所有文件，按照时间降序
				String sql = ("SELECT * from Files WHERE public=1 ORDER BY date DESC");
				query = conn.prepareStatement(sql);
			} else if (type.equals("分享")) {
				// 展示自己分享的文件
				String sql = ("SELECT * from Files WHERE public=1 and user_key=1 ORDER BY date DESC");
				query = conn.prepareStatement(sql);
			} else if (type.equals("其他")) {
				// 展示自己其他的文件
				String sql = ("SELECT * FROM Files WHERE type NOT IN ('audio', 'document', 'photo') and public=1 ORDER BY date DESC");
				query = conn.prepareStatement(sql);
			} else {
				String sql = ("SELECT * from Files WHERE type=? and public=1 ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setString(1, type);
			}
			//管理员权限
		} else if (id == 0) {
			if (type.equals("查询")) {
				// 模糊查询，同时查询自己的文件与共享文件
				String sql = ("SELECT * from Files where name like ? ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setString(1, "%" + name + "%");
			} else if (type.equals("默认")) {
				// 默认展示，显示自己的所有文件，按照时间降序
				String sql = ("SELECT * from Files ORDER BY date DESC");
				query = conn.prepareStatement(sql);
			} else if (type.equals("分享")) {
				// 展示分享的文件
				String sql = ("SELECT * from Files WHERE public=1 ORDER BY date DESC");
				query = conn.prepareStatement(sql);
			} else if (type.equals("其他")) {
				// 展示自己其他的文件
				String sql = ("SELECT * FROM Files WHERE type NOT IN ('audio', 'document', 'photo') ORDER BY date DESC");
				query = conn.prepareStatement(sql);
			} else {
				String sql = ("SELECT * from Files WHERE type=? ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setString(1, type);
			}
		} else {
			if (type.equals("查询")) {
				// 模糊查询，同时查询自己的文件与共享文件
				String sql = ("SELECT * from Files where name like ? and (user_key=? or public=1) ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setString(1, "%" + name + "%");
				query.setInt(2, id);
			} else if (type.equals("默认")) {
				// 默认展示，显示自己的所有文件，按照时间降序
				String sql = ("SELECT * from Files WHERE user_key=? ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setInt(1, id);
			} else if (type.equals("分享")) {
				// 展示自己分享的文件
				String sql = ("SELECT * from Files WHERE user_key=? and public=1 ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setInt(1, id);
			} else if (type.equals("其他")) {
				// 展示自己其他的文件
				String sql = ("SELECT * FROM Files WHERE type NOT IN ('audio', 'document', 'photo') and user_key=? ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setInt(1, id);
			} else {
				String sql = ("SELECT * from Files WHERE type=? and user_key=? ORDER BY date DESC");
				query = conn.prepareStatement(sql);
				query.setString(1, type);
				query.setInt(2, id);
			}
		}
		ResultSet rs = query.executeQuery();
		List<FileInfo> files = new ArrayList<>();
		// 展开结果集数据库
		while (rs.next()) {
			FileInfo file = new FileInfo();
			file.setId(rs.getInt("id"));
			file.setName(rs.getString("name"));
			file.setType(rs.getString("type"));
			file.setPath(rs.getString("path"));
			file.setSize(rs.getDouble("size"));
			file.setDate(rs.getString("date"));
			file.setUser_key(rs.getInt("user_key"));
			file.setMd5(rs.getString("md5"));
			file.setShare(rs.getInt("public"));
			files.add(file);
		}
		Mysql.CloseConn(query, rs, conn);
		return files;
	}

	// 权限返回,传入用户ID，返回权限对象
	public PermissInfo GetPermiss(int id) throws SQLException {
		Connection conn = Mysql.GetConn();
		// 构造SQL语句
		String sql = ("SELECT * from Permiss WHERE user_id=?");
		PreparedStatement query = conn.prepareStatement(sql);
		query.setInt(1, id);
		ResultSet rs = query.executeQuery();
		// 展开结果集数据库
		int[] p_ar = null;
		if (rs.next()) {
			p_ar = new int[] { rs.getInt("user_id"), rs.getInt("login"), rs.getInt("write"), rs.getInt("delete"),
					rs.getInt("ai"), rs.getInt("rename"), rs.getInt("upload"), rs.getInt("download"),
					rs.getInt("share") };
		} else {
			p_ar = new int[9];
		}
		Mysql.CloseConn(query, rs, conn);
		PermissInfo permiss = new PermissInfo(p_ar[0], p_ar[1], p_ar[2], p_ar[3], p_ar[4], p_ar[5], p_ar[6], p_ar[7],
				p_ar[8]);
		return permiss;
	}

	// 已使用存储空间容量返回
	public int GetStorage(int id) throws SQLException {
		Connection conn = Mysql.GetConn();
		// 构造SQL语句
		String sql = ("SELECT FLOOR(sum(size)) 'storage' from Files WHERE user_key=?");
		PreparedStatement query = conn.prepareStatement(sql);
		query.setInt(1, id);
		ResultSet rs = query.executeQuery();
		int Storage = 0;
		// 展开结果集数据库
		if (rs.next()) {
			Storage = rs.getInt("storage");
		} else {
			Storage = 1;
		}
		Mysql.CloseConn(query, rs, conn);
		return Storage;
	}

	// 更新表数据的通用方法
	public boolean update_SQL(String table, String sqls, int id) {
		Connection conn = null;
		PreparedStatement preparedStatement = null;
		try {
			conn = Mysql.GetConn();
			String sql = String.format("UPDATE %s SET %s where id=%d", table, sqls, id);
			preparedStatement = conn.prepareStatement(sql);
			// preparedStatement.setObject(1, table);
			// preparedStatement.setObject(2, sqls);
			// preparedStatement.setInt(3, id);
			int count = preparedStatement.executeUpdate();
			return count == 1;
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			if (preparedStatement != null) {
				try {
					preparedStatement.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
				try {
					conn.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

}
